# 数据层 订货相关信息加载
import datetime

# 订货单
class OrderForm:
    def __init__(self,orfo_no,orfo_address,orfo_date,orfo_total,orfo_status,orfo_cus_no,orfo_wai_no):
        self.no=orfo_no             # 1订单号
        self.address=orfo_address   # 2收货地址
        self.date=orfo_date         # 3订单日期
        self.total=orfo_total       # 4订单总金额
        self.status=orfo_status     # 5订单状态  0未结算，1已取消，2已处理，3已退费
        self.cus_no=orfo_cus_no     # 6发起订单的顾客号
        self.wai_no=orfo_wai_no     # 7处理订单的客服员号

    def toString(self):
        return "{} {} {} {} {} {} {}".format(self.no,self.address,self.date,self.total,self.status,self.cus_no,self.wai_no)

# 订货细则
class OrderDetail:
    def __init__(self,orfe_no,orfe_carg_no,orfe_num,orfe_orfo_no):
        self.no=orfe_no             # 1订货细则号
        self.carg_no=orfe_carg_no   # 2对应的货物号
        self.num=orfe_num           # 3货物数量
        self.orfo_no=orfe_orfo_no   # 4对应的订货单号

    def toString(self):
        return "{} {} {} {}".format(self.no,self.carg_no,self.num,self.orfo_no)


## 订货单的数据操作
# 新增订货单
def addOrderForm(conn,cur,customer):
    # 获取自增1的订单号
    sqlText = 'select * from orderform;'
    cur.execute(sqlText)
    result = cur.fetchall()
    no = len(result) + 1
    no_str = str(no).zfill(5)
    # 获取当前时间
    curTime = datetime.datetime.now().strftime("%Y-%m-%d %H:%M:%S")
    # print(curTime)
    # total置为0，status置为0，wai_no置为空字符串，插入数据
    sqlText = "insert into orderform(orfo_no,orfo_address,orfo_date,orfo_total,orfo_status,orfo_cus_no) values ('{}','{}','{}',{},{},'{}');".format(no_str, customer.address, curTime, 0,0,customer.no)
    cur.execute(sqlText)
    # 提交修改
    conn.commit()
    return no_str


# 删除订货单，不允许订货单被删除

# 查找订货单，根据订单号
def selOrderFormByOrfo_no(cur,orfo_no):
    sqlText = 'select * from orderform where orfo_no="{}";'.format(orfo_no)
    cur.execute(sqlText)
    result = cur.fetchone()
    if result:
        return OrderForm(result[0], result[1], result[2], result[3], result[4], result[5], result[6])
    else:
        return None

# 查找订货单，根据时间 year
def selOrderFormByDateYear(cur,year):
    sqlText="select * from orderform where year(orfo_date)='{}';".format(year)
    cur.execute(sqlText)
    return cur.fetchall()

# 查找订货单，根据时间 year、month
def selOrderFormByDateYearMonth(cur,year,month):
    sqlText="select * from orderform where year(orfo_date)='{}' and month(orfo_date)='{}';".format(year,month)
    cur.execute(sqlText)
    return cur.fetchall()

# 查找订货单，根据时间 year、month、wai_no
def selOrderFormByDateYearMonthAndWai_no(cur,year,month,wai_no):
    sqlText="select * from orderform where year(orfo_date)='{}' and month(orfo_date)='{}' and orfo_wai_no='{}';".format(year,month,wai_no)
    cur.execute(sqlText)
    return cur.fetchall()


# 查找订货单，根据时间 year、month、day
def selOrderFormByDateYearMonthDay(cur,year,month,day):
    sqlText="select * from orderform where year(orfo_date)='{}' and month(orfo_date)='{}' and day(orfo_date)='{}';".format(year,month,day)
    cur.execute(sqlText)
    return cur.fetchall()



# 查找订货单，根据顾客号
def selOrderFormByCus_no(cur,cus_no):
    sqlText = 'select * from orderform where orfo_cus_no="{}";'.format(cus_no)
    cur.execute(sqlText)
    return cur.fetchall()

# 查找订货单，根据客服员号
def selOrderFormByWai_no(cur,wai_no):
    sqlText = 'select * from orderform where orfo_wai_no="{}";'.format(wai_no)
    cur.execute(sqlText)
    return cur.fetchall()

# 查找所有订货单
def selAllOrderForm(cur):
    sqlText = 'select * from orderform;'
    cur.execute(sqlText)
    return cur.fetchall()

# 更改订货单状态
def modiOrderFormStatus(conn,cur,orderform,status):
    if orderform:
        sqlText = "UPDATE orderform SET orfo_status = {} WHERE orfo_no = '{}';".format(status, orderform.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 更改订货单总金额
def modiOrderFormTotal(conn,cur,orderform,total):
    if orderform:
        sqlText = "UPDATE orderform SET orfo_total = {} WHERE orfo_no = '{}';".format(total, orderform.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

# 更改处理订货单的客服员
def modiOrderFormWai_no(conn,cur,orderform,wai_no):
    if orderform:
        # print(wai_no)
        # print(orderform.no)
        sqlText = "UPDATE orderform SET orfo_wai_no = '{}' WHERE orfo_no = '{}';".format(wai_no, orderform.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0




## 订货细则的数据操作
# 新增订货细则
def addOrderFormDetail(conn,cur,orderForm,cargo,num):
    # 获取自增1的订单号
    sqlText = 'select * from orderdetail;'
    cur.execute(sqlText)
    result = cur.fetchall()
    no = len(result) + 1
    no_str = str(no).zfill(5)
    # 插入数据
    sqlText = "insert into orderdetail values ('{}','{}',{},'{}');".format(no_str, cargo.no, num,orderForm.no)
    cur.execute(sqlText)
    conn.commit()

# 删除订货细则，不允许删除

# 查找订货细则，根据订单细则号
def selOrderDetailByOrde_no(cur,orde_no):
    sqlText = 'select * from orderdetail where orde_no="{}";'.format(orde_no)
    cur.execute(sqlText)
    result = cur.fetchone()
    if result:
        return OrderDetail(result[0], result[1], result[2], result[3])
    else:
        return None

# 查根据订单号查找订货细则
def selOrderDetialByorfo_no(cur,orfo_no):
    sqlText = 'select * from orderdetail where orde_orfo_no="{}";'.format(orfo_no)
    cur.execute(sqlText)
    return cur.fetchall()

# 根据货物号查找订单细则
def selOrderDetialByCarg_no(cur,carg_no):
    sqlText = 'select * from orderdetail where orde_carg_no="{}";'.format(carg_no)
    cur.execute(sqlText)
    return cur.fetchall()

# 更改货物的数量
def modiOrderDetailNum(conn,cur,orderdetail,num):
    if orderdetail:
        sqlText = "UPDATE orderdetail SET orde_num = {} WHERE orfo_no = '{}';".format(num, orderdetail.no)
        cur.execute(sqlText)
        conn.commit()
        return 1
    else:
        return 0

if __name__ == '__main__':
    import ConnDBAndCloseDB
    conn,cur=ConnDBAndCloseDB.connectDB()
    # import UserDataLoad
    # customer=UserDataLoad.selCustomerByUser_no(cur,'100001')
    # addOrderForm(conn,cur,customer)
    orderform=selOrderFormByOrfo_no(cur,'00001')
    # print((datetime.datetime.now()-orderform.date).seconds)
    # print(customer.toString())

    ConnDBAndCloseDB.closeDB(cur,conn)